﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BarcodeDelay.Transaction
{
    public sealed class DbOperator : IDisposable
    {
        private DbOperatorFactory factory;

        private static readonly object obj = new object();

        public DbOperator(DbOperatorFactory _factory, DbConnection _connection)
        {
            this.factory = _factory;
            this.connection = _connection;
        }

        private DbConnection connection;
        public DbConnection Connection
        {
            get { return this.connection; }
        }

        private String name;
        public String Name
        {
            get { return this.name; }
            set { this.name = value; }
        }

        private DbDataReader reader;
        public DbDataReader Reader
        {
            get { return this.reader; }
            set { this.reader = value; }
        }

        private int affectRows;
        public int AffectRows
        {
            get { return this.affectRows; }
            set { this.affectRows = value; }
        }

        public ConnectionState GetConnectionState()
        {
            return this.connection.State;
        }

        /// <summary>
        /// 执行Select查询的接口方法
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="maps"></param>
        public void ExecuteQuery(String sql, List<DbParameter> paramsList = null)
        {
            try
            {
                this.DoQuery(sql, paramsList);
            }
            catch (Exception e)
            {
                //Logger.LogError("DbOperator.ExecuteQuery", "Catch an exception : " + e.Message);
            }
        }

        /// <summary>
        /// 执行Insert， Update， Delete的接口方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramsList"></param>
        /// <returns></returns>
        public int ExecuteUpdate(string sql, List<DbParameter> paramsList = null)
        {
            int row = 0;

            DbConnection mysqlconn = this.Connection;
            if (mysqlconn == null || mysqlconn.State != ConnectionState.Open)
            {
                return -1;
            }

            try
            {
                using (DbCommand dbCommand = CreateDbCommand(sql))
                {
                    if (paramsList != null && paramsList.Count > 0)
                    {
                        foreach (DbParameter param in paramsList)
                        {
                            dbCommand.Parameters.Add(param);
                        }
                    }

                    row = dbCommand.ExecuteNonQuery();

                    this.affectRows = row;
                }
            }
            catch (Exception e)
            {
                //Logger.LogError("DbOperator.ExecuteUpdate", "Catch an exception : " + e.Message);
                //Logger.LogError("DbOperator.ExecuteUpdate sql", "Catch an exception sql: " + sql);
            }

            return row;
        }

        /// <summary>
        /// 执行事务更改方法
        /// </summary>
        /// <param name="sqls"></param>
        /// <param name="paramsList"></param>
        /// <returns></returns>
        public int ExecuteUpdateTrans(List<String> sqls, int CommitSize = 1000)
        {
            int successNumber = 0;
            DbTransaction dbt = null;
            DbConnection mysqlconn = this.Connection;
            if (mysqlconn == null || mysqlconn.State != ConnectionState.Open)
            {
                return -1;
            }

            try
            {
                using (DbCommand dbCommand = this.factory.DbProviderFactory.CreateCommand())
                {
                    dbCommand.Connection = mysqlconn;
                    dbt = mysqlconn.BeginTransaction();
                    dbCommand.Transaction = dbt;
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        if (sqls[i].Trim() != "")
                        {
                            dbCommand.CommandText = sqls[i];
                            int row = dbCommand.ExecuteNonQuery();
                            if (row > 0) successNumber += row;
                        }
                        if ((i + 1) > 0 && ((i + 1) % CommitSize == 0 || i == sqls.Count - 1))
                        {
                            dbt.Commit();
                            dbt = mysqlconn.BeginTransaction();
                        }
                        //ExcelImportHelper.Instance.changeProgress("正在插入数据", i + 1, sqls.Count);
                    }



                    this.affectRows = successNumber;
                }
            }
            catch (Exception e)
            {
                if (dbt != null)
                {
                    dbt.Rollback();
                }
                successNumber = 0;
                //Logger.LogError("DbOperator.ExecuteUpdate", "Catch an exception : " + e.Message);
            }

            return successNumber;
        }

        /// <summary>
        /// 执行scalar的接口方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramsList"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, List<DbParameter> paramsList = null)
        {
            object executeObj = null;

            DbConnection mysqlconn = this.Connection;
            if (mysqlconn == null || mysqlconn.State != ConnectionState.Open)
            {
                return -1;
            }

            try
            {
                using (DbCommand dbCommand = CreateDbCommand(sql))
                {
                    if (paramsList != null && paramsList.Count > 0)
                    {
                        foreach (DbParameter param in paramsList)
                        {
                            dbCommand.Parameters.Add(param);
                        }
                    }


                    executeObj = dbCommand.ExecuteScalar();

                }
            }
            catch (Exception e)
            {
                //Logger.LogError("DbOperator.ExecuteScalar", "Catch an exception : " + e.Message);
            }

            return executeObj;
        }

        /// <summary>
        /// 生成一个参数对象
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public DbParameter CreateDbParameter(string name, object value, DbType type, int size = 0)
        {
            DbParameter param = DoCreateDbPamameter();
            param.ParameterName = name;
            param.Value = value;
            param.DbType = type;
            if (size > 0)
            {
                param.Size = size;
            }

            return param;
        }

        public void Dispose()
        {
            if (this.reader != null)
            {
                this.reader.Close();
                this.reader = null;
            }

            if (this.connection != null)
            {
                this.connection.Close();
            }
        }

        /// <summary>
        /// 设置DbCommand参数，执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private void DoQuery(string sql, List<DbParameter> paramsList)
        {
            DbDataReader _reader = null;

            DbConnection dbconn = this.Connection;
            if (dbconn == null || dbconn.State != ConnectionState.Open)
            {
                return;
            }
            try
            {
                using (DbCommand dbCommand = CreateDbCommand(sql))
                {
                    dbCommand.CommandTimeout = 45;
                    if (paramsList != null && paramsList.Count > 0)
                    {
                        foreach (DbParameter param in paramsList)
                        {
                            dbCommand.Parameters.Add(param);
                        }
                    }
                    _reader = dbCommand.ExecuteReader();
                }

                this.reader = _reader;
            }
            catch (Exception e)
            {
                //SimbaCameraScale.Common.Log.Logger.LogError(null, e.Message);
                if (_reader != null)
                {
                    _reader.Close();
                }
                _reader = null;
            }
        }

        private DbCommand CreateDbCommand(string sql)
        {
            DbCommand command = this.factory.DbProviderFactory.CreateCommand();
            command.Connection = this.connection;
            command.CommandText = sql;
            return command;
        }

        private DbParameter DoCreateDbPamameter()
        {
            return this.factory.DbProviderFactory.CreateParameter();
        }
        //////////////////////////////////////////////////////////////////////////END OF CLASS
    }
}